using System;
using System.Collections; 
using System.Collections.Specialized;  
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace HermesPortal.Core.DAO
{
    internal class RealmDAO
    {
        private RealmDAO(){}
        public static List< Realm >  GetRealmsBySaga( int sagaID )
        {
            List<Realm> realms = new List<Realm>();
            SqlDataReader reader = null;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("RealmsGet", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@SagaID", sagaID);
                conn.Open();
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Realm realm = LoadRealm(reader);
                    realms.Add(realm);
                }

            }

            return realms;
        }

        public static List<Realm> GetRealmsBySaga(int sagaID, int creatorID)
        {
            List<Realm> realms = new List<Realm>();
            SqlDataReader reader = null;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("RealmsGet", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@SagaID", sagaID);
                cmd.Parameters.AddWithValue("@CreatorID", creatorID);
                conn.Open();
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Realm realm = LoadRealm(reader);
                    realms.Add(realm);
                }

            }

            return realms;
        }

      

        public  static string GetRealmDescription( int realmId)
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("RealmDescriptionGet", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@RealmID", realmId);
                conn.Open();
                object objDescription= cmd.ExecuteScalar();
                if (objDescription != null)
                    return objDescription.ToString();
                else
                    return null;
            }

        }

        /// <summary>
        /// Returns all Realms for a saga regardless of privacy setting
        /// </summary>
        /// <param name="sagaId"></param>
        /// <param name="RealmId"></param>
        /// <returns></returns>
        internal static List<Realm> AdminGetRealms(int sagaId, int realmId)
        {
            List<Realm> realms = new List<Realm>();
            SqlDataReader reader = null;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("RealmsAdminGetSubNodes", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@SagaID", sagaId);
                cmd.Parameters.AddWithValue("@RealmID", realmId);
                conn.Open();
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Realm realm = LoadRealm(reader);
                    realms.Add(realm);
                }

            }
            return realms;

        }

        /// <summary>
        /// Returns all Realms for a saga regardless of privacy setting
        /// </summary>
        /// <param name="sagaId"></param>
        /// <param name="RealmId"></param>
        /// <returns></returns>
        internal static List<Realm> AdminGetRealms(int sagaId)
        {
            List<Realm> realms = new List<Realm>();
            SqlDataReader reader = null;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("RealmsGet", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@SagaID", sagaId);
                conn.Open();
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Realm realm = LoadRealm(reader);
                    realms.Add(realm);
                }

            }
            return realms;

        }
        /// <summary>
        /// Returns all Realms for this Saga
        /// </summary>
        /// <param name="realmID"></param>
        /// <returns></returns>
        internal static Realm GetRealm(int realmID)
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            {
                Realm realm = null;
                SqlCommand cmd = new SqlCommand("RealmGet", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@RealmID", realmID);
                conn.Open();
                SqlDataReader  reader  = cmd.ExecuteReader();
                if (reader.Read())
                    realm = LoadRealm(reader);
                return realm;
            }
        }


        /// <summary>
        /// Returns a List of Child Realm Nodes given thier Parents RealmID 
        /// Note: RealmnID 0 will return all the Sagas Root node Realms 
        /// </summary>
        /// <param name="sagaID"></param>
        /// <returns>List<Realm></returns>
        internal static List<Realm> GetRealms(int sagaID, int creatorID)
        {
            List<Realm> realms = new List<Realm>();
            SqlDataReader reader = null;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("RealmsGet", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@SagaID", sagaID);
                cmd.Parameters.AddWithValue("@CreatorID", creatorID);
                conn.Open();
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Realm realm = LoadRealm(reader);
                    realms.Add(realm);
                }

            }

            return realms;
            
        }

        internal static List<Realm> GetRealms(int sagaID, int realmID, int creatorID)
        {
            List<Realm> realms = new List<Realm>();
            SqlDataReader reader = null;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("RealmsGetSubNodes", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@SagaID", sagaID);
                cmd.Parameters.AddWithValue("@RealmID", realmID);
                cmd.Parameters.AddWithValue("@CreatorID", creatorID);
                conn.Open();
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Realm realm = LoadRealm(reader);
                    realms.Add(realm);
                }

            }

            return realms;
        }

        /// <summary>
        ///  Returns the Title and RealmID for SubRealms of the current chosen realm
        /// </summary>
        /// <param name="sagaID"></param>
        /// <param name="realmID"></param>
        /// <returns>StringDictionary</returns>
        internal static  StringDictionary GetRealmSubNodesInfo(int sagaID, int realmID, int creatorID)
        {
            StringDictionary listRealms = new StringDictionary();
            SqlDataReader reader = null;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("RealmsGetSubNodesSummary", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@SagaID", sagaID);
                cmd.Parameters.AddWithValue("@RealmID", realmID);
                cmd.Parameters.AddWithValue("@CreatorID", creatorID);  
                conn.Open();
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    listRealms.Add(reader["RealmID"].ToString(), reader["Title"].ToString());
                }

            }

            return listRealms;

        }

        /// <summary>
        ///  Returns the Title and RealmID for SubRealms of the current chosen realm
        /// </summary>
        /// <param name="sagaID"></param>
        /// <param name="realmID"></param>
        /// <returns>StringDictionary</returns>
        internal static StringDictionary GetRealmSubNodesInfo(int sagaID, int realmID)
        {
            StringDictionary listRealms = new StringDictionary();
            SqlDataReader reader = null;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("RealmsGetSubNodesSummary", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@SagaID", sagaID);
                cmd.Parameters.AddWithValue("@RealmID", realmID);
               
                conn.Open();
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    listRealms.Add(reader["RealmID"].ToString(), reader["Title"].ToString());
                }

            }

            return listRealms;

        }
        /// <summary>
        ///  returns the Title and RealmID for all Realms in the current Saga
        /// </summary>
        /// <param name="sagaID"></param>
        /// <param name="realmID"></param>
        /// <returns>StringDictionary</returns>
        internal static StringDictionary GetRealmSubNodesInfoAdmin(int sagaID)
        {
            StringDictionary listRealms = new StringDictionary();
            SqlDataReader reader = null;

            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("RealmGetSubNodes", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@SagaID", sagaID);
               
                conn.Open();
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    listRealms.Add(reader["RealmID"].ToString(), reader["Title"].ToString());
                }

            }

            return listRealms;

        }


        internal static void UpdateRealm(Realm realm)
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("RealmUpdate", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@RealmID", realm.RealmID   );
                cmd.Parameters.AddWithValue("@Title", realm.Title);
                cmd.Parameters.AddWithValue("@Summary", realm.Summary);
                cmd.Parameters.AddWithValue("@Description", realm.Description);
                cmd.Parameters.AddWithValue("@ParentID", realm.ParentID);
                cmd.Parameters.AddWithValue("@IsPrivate", realm.IsPrivate);
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();  
            }
        }

        #region private helpers
        private static Realm LoadRealm(SqlDataReader reader)
        {
            Realm realm = null;
            if (reader != null && !reader.IsClosed)
            {
                realm = new Realm();
                realm.RealmID  = (int)reader["RealmID"];
                if (reader["SagaID"] != DBNull.Value) realm.SagaID = (int)reader["SagaID"];
                if (reader["Title"] != DBNull.Value) realm.Title = (string)reader["Title"];
                if (reader["Summary"] != DBNull.Value) realm.Summary = (string)reader["Summary"];
                if (reader["ParentID"] != DBNull.Value) realm.ParentID  = (int)reader["ParentID"];
                if (reader["CreatorID"] != DBNull.Value) realm.CreatorID = (int)reader["CreatorID"];
                if (reader["IsPrivate"] != DBNull.Value) realm.IsPrivate = Convert.ToBoolean (reader["IsPrivate"]);

            }

            return realm;
        }
        #endregion 
    
        
    
       
    
        internal static int AddRealm(Realm realm)
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("RealmInsert", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@SagaID", realm.SagaID);
                cmd.Parameters.AddWithValue("@CreatorID", realm.CreatorID );
                cmd.Parameters.AddWithValue("@Title", realm.Title);
                cmd.Parameters.AddWithValue("@Summary", realm.Summary);
                cmd.Parameters.AddWithValue("@Description", realm.Description);
                cmd.Parameters.AddWithValue("@ParentID", realm.ParentID);
                cmd.Parameters.AddWithValue("@IsPrivate", realm.IsPrivate);
                conn.Open();

               
               object objRealmID =  cmd.ExecuteScalar();
                conn.Close();
                return Convert.ToInt32(objRealmID); 
            }
        }
    }
}
